﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data;

namespace ADOTestingLibrary
{
    public class Class1
    {
        private SqlConnection cn;
        public Class1()
        {
            cn = new SqlConnection("Data Source=MTPC248\\YOGA;Initial Catalog=Q1_2013;Integrated Security=True");
        }

        public List<Product> Select()
        {
            List<Product> list = new List<Product>();

            //SqlConnection cn = new SqlConnection("Data Source=MTPC248\\YOGA;Initial Catalog=Q1_2013;Integrated Security=True");
            try
            {
                this.cn.Open();
                string query = "SELECT * FROM dbo.Products";
                SqlCommand cm = new SqlCommand(query, this.cn);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                //DataSet ds = new DataSet("ds1");

                //da.Fill(ds);

                //var productID = ds.Tables[0].Columns[0];

                //SqlDataReader dr = new SqlDataReader();
                Product obj;
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    obj = new Product();
                    obj.ProductID = dr.GetInt32(0);
                    obj.ProductName = dr.GetString(1);
                    obj.Price = dr.GetDecimal(2);
                    list.Add(obj);
                }
            }
            catch (Exception ex)
            {
                this.cn.Close();
            }
            finally
            {
                this.cn.Close();
            }
            return list;
        }

        public List<Product> Method1()
        {
            List<Product> list = new List<Product>();

            try
            {
                this.cn.Open();
                string query = "SELECT * FROM dbo.Products WHERE Price < 200 and ProductName LIKE 'B%'";
                SqlCommand cm = new SqlCommand(query, this.cn);
                SqlDataAdapter da = new SqlDataAdapter(cm);

                Product obj;
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    obj = new Product();
                    obj.ProductID = dr.GetInt32(0);
                    obj.ProductName = dr.GetString(1);
                    obj.Price = dr.GetDecimal(2);
                    list.Add(obj);
                }
            }
            catch (Exception ex)
            {
                this.cn.Close();
            }
            finally
            {
                this.cn.Close();
            }
            return list;
        }

        public List<Product> Method2()
        {
            List<Product> list = new List<Product>();

            try
            {
                this.cn.Open();
                string query = "SELECT * FROM dbo.Products WHERE ProductID = 3";
                SqlCommand cm = new SqlCommand(query, this.cn);
                SqlDataAdapter da = new SqlDataAdapter(cm);

                Product obj;
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    obj = new Product();
                    obj.ProductID = dr.GetInt32(0);
                    obj.ProductName = dr.GetString(1);
                    obj.Price = dr.GetDecimal(2);
                    list.Add(obj);
                }
            }
            catch (Exception ex)
            {
                this.cn.Close();
            }
            finally
            {
                this.cn.Close();
            }
            return list;
        }

        public List<Product> Method3(string productName, decimal price, int productId)
        {
            List<Product> list = new List<Product>();

            try
            {
                this.cn.Open();
                string query = "SELECT * FROM dbo.Products WHERE ProductID = @productId";
                string query2 = "UPDATE dbo.Products SET ProductName = @productName, Price = @price WHERE ProductID = @productId";
                //string query2 = "UPDATE dbo.Products SET ProductName = 'A1001', Price = 137 WHERE ProductID = 1";
                SqlCommand cm1 = new SqlCommand(query2, this.cn);
                cm1.Parameters.AddWithValue("@productName", productName);
                cm1.Parameters.AddWithValue("@price", price);
                cm1.Parameters.AddWithValue("@productId", productId);            
                cm1.ExecuteNonQuery();
                SqlCommand cm2 = new SqlCommand(query, this.cn);
                cm2.Parameters.AddWithValue("@productId", productId);
                SqlDataAdapter da = new SqlDataAdapter(cm2);

                Product obj;
                SqlDataReader dr = cm2.ExecuteReader();
                while (dr.Read())
                {
                    obj = new Product();
                    obj.ProductID = dr.GetInt32(0);
                    obj.ProductName = dr.GetString(1);
                    obj.Price = dr.GetDecimal(2);
                    list.Add(obj);
                }
            }
            catch (Exception ex)
            {
                this.cn.Close();
                Console.WriteLine(""+ex.Message);
            }
            finally
            {
                this.cn.Close();
            }
            return list;
        }

        public bool Method4(string productName, decimal price, int productId)
        {
            this.cn.Open();
            SqlTransaction transact = this.cn.BeginTransaction();
            try
            {
                string query = "UPDATE dbo.Products SET ProductName = @productName, Price = @price WHERE ProductName LIKE 'E%'";
                //string query = "UPDATE dbo.Products SET ProductName = @productName, Price = @price WHERE ProductID = @productId";
                SqlCommand cm = new SqlCommand(query, this.cn,transact);
                cm.Parameters.AddWithValue("@productName", productName);
                cm.Parameters.AddWithValue("@price", price);
                cm.Parameters.AddWithValue("@productId", productId); 
                cm.ExecuteNonQuery();
                transact.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transact.Rollback();
                this.cn.Close();
                Console.WriteLine("" + ex.Message);
                return false;
            }
            finally
            {
                this.cn.Close();
            }
        }

        public void Method5(int productId)
        {
            try
            {
                this.cn.Open();
                string message = "1 Row Deleted";
                string query = "DELETE dbo.Products WHERE ProductID = @productId";
                SqlCommand cm = new SqlCommand(query, this.cn);
                
                cm.Parameters.AddWithValue("@productId", productId);
                cm.ExecuteNonQuery();
                Console.WriteLine("{0}",message);
            }
            catch (Exception ex)
            {
                this.cn.Close();
                Console.WriteLine("" + ex.Message);
            }
            finally
            {
                this.cn.Close();
            }
        }

        
    }
}
